package com.matt.repository.impl;

import com.matt.commons.utils.QueryUtil;
import com.matt.domain.record.CheckRecord;
import com.matt.foundation.DO.CheckRecordDO;
import com.matt.foundation.DO.ChildrenCheckRecordDO;
import com.matt.foundation.utils.CopyUtils;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.ChildrenCheckRecordRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/25
 * *******************************
 */
@Service
public class ChildrenCheckRecordRepositoryImpl extends BaseNativeSqlRepository implements ChildrenCheckRecordRepository {

    @Override
    public List<ChildrenCheckRecordDO> findByClasses(String classesId) {
        String findChildrenSql = String.format("select c.id as childrenId, c.name as childrenName, c.imgUrl as imgUrl ,c.gender as gender, c.birthday as birthday, e.checkStatus as checkStatus, e.checkTime as checkTime, e.healthyStatus as healthyStatus, e.remark as remark,e.sickName as sickName" +
                        " from children c  LEFT join children_extend_info e on e.childrenId = c.id "+
                        " LEFT JOIN rel_classes_children cc on cc.childrenId = c.id" +
                        " WHERE cc.classesId = '%s' and c.isDisplayForAdmin = false",
                classesId);
        List<ChildrenCheckRecordDO> objectArrayList = sqlObjectList(findChildrenSql, ChildrenCheckRecordDO.class);
        return objectArrayList;
    }

    @Override
    public List<ChildrenCheckRecordDO> findByClassesAndCreated(String classesId,String created) {
        String findChildrenSql = "select c.id as childrenId, c.name as childrenName, c.imgUrl as imgUrl ,c.gender as gender, c.birthday as birthday, " +
                        " e.status as checkStatus, e.created as checkTime, e.healthyStatus as healthyStatus  " +
                        " from children c  LEFT join check_record e on e.childrenId = c.id   " +
                        " LEFT JOIN rel_classes_children cc on cc.childrenId = c.id " +
                        " WHERE cc.classesId = '"+classesId+"' AND  DATE_FORMAT(e.created,'%Y-%m-%d') =  DATE_FORMAT('"+created+"' ,'%Y-%m-%d') " +
                        " and c.isDisplayForAdmin = false  ORDER BY e.created DESC";
        //已有记录小朋友
        List<ChildrenCheckRecordDO> objectArrayList = sqlObjectList(findChildrenSql, ChildrenCheckRecordDO.class);

        String sql = "select c.id as childrenId, c.name as childrenName, c.imgUrl as imgUrl ,c.gender as gender, c.birthday as birthday  " +
                "  from children c   LEFT JOIN rel_classes_children cc on cc.childrenId = c.id   WHERE cc.classesId = '"+classesId+"'";
        //所有小朋友
        List<ChildrenCheckRecordDO> childrenList = sqlObjectList(sql, ChildrenCheckRecordDO.class);
        for (int i = 0; i < childrenList.size(); i++) {
            for (int j = 0; j < objectArrayList.size(); j++) {
                if (StringUtils.equals(childrenList.get(i).getChildrenId(),objectArrayList.get(j).getChildrenId())){
                    CopyUtils.copyProperties(objectArrayList.get(j),childrenList.get(i));
                }
            }
        }
        return childrenList;
    }


    @Override
    public List<ChildrenCheckRecordDO>  findByChildrenIdsAndCreated(List<String> childrenIds, Date beginDate, Date endDate){
        String findChildrenSql = "SELECT e.childrenId, e.STATUS AS checkStatus, " +
                " e.checkInTime, e.checkOutTime,  " +
                " e.created AS checkTime,  " +
                " e.healthyStatus AS healthyStatus   " +
                " FROM  " +
                " check_record e   " +
                " WHERE e.childrenId in (:1)  " +
                " AND e.created >= :2 and e.created < :3 " +
                " GROUP BY e.childrenId, e.groupFlag" +
                " ORDER BY e.created DESC";
        //已有记录小朋友

        List params = new ArrayList();
        params.add(childrenIds);
        params.add(beginDate);
        params.add(endDate);

        List<ChildrenCheckRecordDO> objectArrayList = sqlObjectList(findChildrenSql, params, ChildrenCheckRecordDO.class);

        return objectArrayList;
    }


    @Override
    public List<ChildrenCheckRecordDO> findByClassesIn(List<String> classesIds) {
        String findChildrenSql = "select c.id as childrenId, c.name as childrenName, c.imgUrl as imgUrl ,c.gender as gender, c.birthday as birthday, " +
                "e.checkStatus as checkStatus, e.checkTime as checkTime, e.healthyStatus as healthyStatus, e.remark as remark,e.sickName as sickName, cc.classesId as classesId" +
                        " from children c  LEFT join children_extend_info e on e.childrenId = c.id "+
                        " LEFT JOIN rel_classes_children cc on cc.childrenId = c.id" +
                        " WHERE cc.classesId in :1 and c.isDisplayForAdmin = false";

        List params = new ArrayList();

        params.add(classesIds);

        List<ChildrenCheckRecordDO> objectArrayList = sqlObjectList(findChildrenSql, params, ChildrenCheckRecordDO.class);

        return objectArrayList;
    }

    @Override
    public Integer findClienteleCountByDateAndAreaId(String beginDate, String endDate, String areaId) {
        String findSql = String.format(" select count(DISTINCT(cr.childrenId)) "
                + " from check_record cr left join children c on c.id =cr.childrenId where  cr.checkInTime >= '%s' and cr.checkInTime <= '%s' and c.aid ='%s' ", beginDate, endDate, areaId);
        List objectArrayList = sqlSingleList(findSql);
        return Integer.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Integer findTeacherCountByDateAndAreaId(String beginDate, String endDate, String areaId) {
        String findSql = String.format(" select count(rct.staffId) from rel_classes_teacher rct where rct.classesId in  "
                + " (select DISTINCT(rcc.classesId) as classesId from check_record cr left join children c on c.id =cr.childrenId LEFT join rel_classes_children rcc on rcc.childrenId = cr.childrenId where " +
                "  cr.checkInTime >= '%s' and cr.checkInTime <= '%s' and c.aid ='%s') ", beginDate, endDate, areaId);

        List objectArrayList = sqlSingleList(findSql);
        return Integer.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Integer findCheckInCountByDateAndAreaId(String date, String areaId) {
        String findSql = String.format(" select count(DISTINCT(cr.childrenId)) "
                + " from check_record cr left join children c on c.id =cr.childrenId where (cr.status=1 or cr.status=2)and date(cr.checkInTime) = date('%s') and c.aid ='%s' ", date, areaId);
        List objectArrayList = sqlSingleList(findSql);
        return Integer.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Integer findCheckOutCountByDateAndAreaId(String date, String areaId) {
        String findSql = String.format(" select count(DISTINCT(cr.childrenId)) "
                + " from check_record cr left join children c on c.id =cr.childrenId where (cr.status=1 or cr.status=2) and date(cr.checkOutTime) = date('%s')  and c.aid ='%s' ", date, areaId);
        List objectArrayList = sqlSingleList(findSql);
        return Integer.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Integer findCheckInCountByDateAndClienteleIds(String date, List<String> clienteleIds) {
        String findSql = String.format("select count(DISTINCT(cr.childrenId)) " +
                " from check_record cr left join children c on c.id =cr.childrenId where (cr.status=1 or cr.status=2) and date(cr.checkInTime) = date('%s') and c.crmClienteleId IN(%s)",date, QueryUtil.joinQuota(clienteleIds));
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

    @Override
    public Integer findCheckInCountByDateAndClienteleId(String clienteleId, String beginDate, String endDate) {
        String findSql ="SELECT COUNT(1) FROM (select count(1)  " +
                " from check_record cr left join children c on c.id =cr.childrenId where (cr.status=1 or cr.status=2) " +
                " and c.crmClienteleId = '"+clienteleId+"' and cr.checkInTime >= DATE_FORMAT('"+beginDate+"','%Y-%m-%d %H:%i:%S') and cr.checkInTime <= DATE_FORMAT('"+endDate+"','%Y-%m-%d %H:%i:%S') " +
                "  GROUP BY DATE_FORMAT(cr.checkInTime,'%Y-%m-%d') ) a ";
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }


    @Override
    public Integer findLeaveCountByDateAndClienteleId(String clienteleId, String beginDate, String endDate) {
        String findSql ="SELECT COUNT(1) FROM (select count(1)  " +
                " from check_record cr left join children c on c.id =cr.childrenId where cr.status=4  " +
                " and c.crmClienteleId = '"+clienteleId+"' and cr.checkInTime >= DATE_FORMAT('"+beginDate+"','%Y-%m-%d %H:%i:%S') and cr.checkInTime <= DATE_FORMAT('"+endDate+"','%Y-%m-%d %H:%i:%S') " +
                "  GROUP BY DATE_FORMAT(cr.checkInTime,'%Y-%m-%d') ) a ";
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

    @Override
    public Integer getTeacherCheckInCount(String staffId, String date) {
        String findSql = String.format("SELECT count(DISTINCT(cr.childrenId)) " +
                " FROM check_record as cr INNER JOIN children as ch ON ch.id=cr.childrenId " +
                " where date(cr.checkInTime)=date('%s') and (cr.status=1 or cr.status=2)" +
                " and ch.id IN (SELECT DISTINCT(rcc.childrenId) from rel_classes_teacher as rct INNER JOIN rel_classes_children as rcc on rct.classesId=rcc.classesId where rct.staffId='%s')",date,staffId);
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

    @Override
    public Integer getTeacherCheckOutCount(String staffId, String date) {
        String findSql = String.format("SELECT count(DISTINCT(cr.childrenId)) " +
                " FROM check_record as cr INNER JOIN children as ch ON ch.id=cr.childrenId " +
                " where date(cr.checkOutTime)=date('%s') and (cr.status=1 or cr.status=2)" +
                " and ch.id IN (SELECT DISTINCT(rcc.childrenId) from rel_classes_teacher as rct INNER JOIN rel_classes_children as rcc on rct.classesId=rcc.classesId where rct.staffId='%s')",date,staffId);
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

    @Override
    public Integer getAreaChildCheckInCount(String areaId, String date) {
        String querySql = String.format("select count(DISTINCT(cr.childrenId)) from check_record as cr inner join children as ch on cr.childrenId=ch.id " +
                " where date(cr.checkInTime)=date('%s') and (cr.status=1 or cr.status=2) and ch.aid='%s' ",date,areaId);
        List list = sqlSingleList(querySql);
        return Integer.parseInt(list.get(0).toString());
    }

    @Override
    public Integer getNurseryCheckInCount(String staffId, String date) {
        String findSql = String.format("SELECT count(DISTINCT(cr.childrenId)) " +
                " FROM check_record as cr INNER JOIN children as ch ON ch.id=cr.childrenId " +
                " where date(cr.checkInTime)=date('%s') and (cr.status=1 or cr.status=2)" +
                " and ch.id IN (SELECT DISTINCT(rcc.childrenId) from rel_classes_nurse as rcn INNER JOIN rel_classes_children as rcc on rcn.classesId=rcc.classesId where rcn.staffId='%s')",date,staffId);
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

    @Override
    public Integer getNurseryCheckOutCount(String staffId, String date) {
        String findSql = String.format("SELECT count(DISTINCT(cr.childrenId)) " +
                " FROM check_record as cr INNER JOIN children as ch ON ch.id=cr.childrenId " +
                " where date(cr.checkOutTime)=date('%s') and (cr.status=1 or cr.status=2)" +
                " and ch.id IN (SELECT DISTINCT(rcc.childrenId) from rel_classes_nurse as rcn INNER JOIN rel_classes_children as rcc on rcn.classesId=rcc.classesId where rcn.staffId='%s')",date,staffId);
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

	@Override
	public List<ChildrenCheckRecordDO> findByAreaId(String areaId) {
		String findChildrenSql = String.format("SELECT	c.id AS childrenId,	c. NAME AS childrenName,	c.imgUrl AS imgUrl,	c.gender AS gender,	c.birthday AS birthday,	e.checkStatus AS checkStatus,	e.checkTime AS checkTime,e.healthyStatus AS healthyStatus,e.remark AS remark,e.sickName AS sickName "
				+ "FROM	children c LEFT JOIN children_extend_info e ON e.childrenId = c.id WHERE " + 
				"	c.aid = '%s' AND c.isDisplayForAdmin = FALSE",
                areaId);
		List<ChildrenCheckRecordDO> objectArrayList = sqlObjectList(findChildrenSql, ChildrenCheckRecordDO.class);
		return objectArrayList;
	}

    @Override
    public List<CheckRecordDO> findCheckRecordByChildrenIdAndMonth(String childrenId,String month) {
        String findSql = String.format("SELECT cs.name as classesName,ch.name as childrenName,cr.created as created,ch.birthday as birthday "+
                "FROM check_record cr "+
                "left join children ch on ch.id=cr.childrenId "+
                "left join rel_classes_children rcc on rcc.childrenId=cr.childrenId "+
                "left join classes cs on cs.id=rcc.classesId "+
                "where month(cr.created)=month('%s-01') and year(cr.created)=year('%s-01') and (cr.status=1 or cr.status=2) "+
                "and rcc.childrenId='%s' GROUP by cr.created",month,month,childrenId);
        List<CheckRecordDO> objectArrayList = sqlObjectList(findSql, CheckRecordDO.class);
        return objectArrayList;
    }


    @Override
    public List<CheckRecord> findCheckRecordsByChildrenIdAndAreaIdAndDate(String childrenId, String areaId, String date){
        String findSql = "SELECT  cr.* FROM " +
                " check_record cr  LEFT JOIN children ch ON ch.id = cr.childrenId " +
                " WHERE cr.childrenId = :1 and ch.aid = :2 and cr.created BETWEEN :3 and :4 ";
//                " GROUP BY cr.created desc";

        List<String> params = new ArrayList<>();
        params.add(childrenId);
        params.add(areaId);
        params.add(String.format("%s 00:00:00", QueryUtil.replaceAllSql(date)));
        params.add(String.format("%s 23:59:59", QueryUtil.replaceAllSql(date)));

        List<CheckRecord> objectArrayList = sqlObjectList(findSql, params, CheckRecord.class);

        return objectArrayList;
    }
}
